package com.jdone.compus.service;


import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.jdone.compus.model.StudentWhitelist;
import com.jdone.compus.repository.StudentWhitelistRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;


@Service
public class StudentWhitelistService {

    @Autowired
    private StudentWhitelistRepository repository;

    @PersistenceContext
    private EntityManager entityManager;

    // Create
    public StudentWhitelist createStudent(StudentWhitelist student) {
        return repository.save(student);
    }

    // Read
    public Optional<StudentWhitelist> getStudentById(Long id) {
        return repository.findById(id);
    }

    // Update
    public StudentWhitelist updateStudent(Long id, StudentWhitelist updatedStudent) {
        Optional<StudentWhitelist> existingStudent = repository.findById(id);
        if (existingStudent.isPresent()) {
            StudentWhitelist student = existingStudent.get();
            student.setName(updatedStudent.getName());
            student.setBuilding(updatedStudent.getBuilding());
            student.setRoom(updatedStudent.getRoom());
            return repository.save(student);
        } else {
            return null;
        }
    }

    // Delete (Soft Delete)
    public void deleteStudent(Long id) {
        Optional<StudentWhitelist> existingStudent = repository.findById(id);
        if (existingStudent.isPresent()) {
            StudentWhitelist student = existingStudent.get();
            student.setIsDeleted(true);
            repository.save(student);
        }
    }

    // Multi-condition Query
    public List<StudentWhitelist> findByNameAndRoom(String name, String room) {
        return repository.findByNameContainingAndRoomContainingAndIsDeletedFalse(name, room);
    }

    /**
     * 根据姓名和房间号进行分页条件查询
     * @param name 姓名（可选）
     * @param room 房间号（可选）
     * @param pageable 分页参数
     * @return 分页结果
     */
    public Page<StudentWhitelist> findByNameAndRoomWithPagination(String name, String room, String studentId, Pageable pageable) {
        return repository.findByNameAndRoom(name, room,  studentId, pageable);
    }

    public void importStudents(MultipartFile file) throws Exception {
        List<StudentWhitelist> students = new ArrayList<>();
        String fileName = file.getOriginalFilename();
        Workbook workbook;

        try (InputStream is = file.getInputStream()) {
            // 根据文件后缀判断是xls还是xlsx
            if (fileName != null && fileName.endsWith(".xls")) {
                workbook = new HSSFWorkbook(is);
            } else if (fileName != null && fileName.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(is);
            } else {
                throw new IllegalArgumentException("不支持的文件格式。请上传xls或xlsx格式的文件。");
            }

            Sheet sheet = workbook.getSheetAt(0);
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    String studentId = getCellValue(row.getCell(1));

                    // 通过学号查找现有记录
                    Optional<StudentWhitelist> existingStudentOpt = repository.findByStudentId(studentId);
                    StudentWhitelist student = existingStudentOpt.orElseGet(StudentWhitelist::new);

                    // 设置学生信息
                    student.setName(getCellValue(row.getCell(0)));
                    student.setStudentId(studentId); // 如果是新学生，设置学号
                    student.setBuilding(getCellValue(row.getCell(2)));
                    student.setRoom(getCellValue(row.getCell(3)));
                    student.setIsDeleted(false);

                    students.add(student);
                }
            }

            // 保存所有学生信息，已存在的记录会被更新
            repository.saveAll(students);
        } catch (Exception e) {
            throw new Exception("解析Excel文件出错：" + e.getMessage());
        }
    }

    private String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                return String.valueOf((long) cell.getNumericCellValue());
            default:
                return "";
        }
    }


    public Optional<StudentWhitelist> findByStudentId(String studentId) {
        return repository.findByStudentId(studentId);
    }

    /**
     * 根据学生号删除记录
     * @param studentId 学生号
     */
    public void deleteByStudentId(String studentId) {
        StudentWhitelist student = repository.findByStudentId(studentId).get();
        repository.delete(student);
    }
}

